import pymysql 


class Stu:
	'''stu表操作类'''

	# 构涵初始化
	def __init__(self):
		self.db = pymysql.connect(
			host = 'localhost',
			user = 'root',
			password='654321',
			charset = 'utf8',
			database= 'test'
				)
		self.cursor = self.db.cursor()

	# 析构关闭数据库
	def __del__(self):
		self.cursor.close()
		self.db.close()

	def findAll(self):
		'''数据查询'''
		try:
			self.cursor.execute("select * from stu;")
			stu_list = self.cursor.fetchall()
			print("="*11,"信息浏览","="*11)
			print("|{:^8s}|{:^10s}\t|{:^8s}|{:^10s}|".format("id","name","age","classid"))
			for student in stu_list:
				id = student[0]
				name = student[1]
				age = student[2]
				classid = student[3]
				print("|{0:^8d}|{1:^9s}\t|{2:^8d}|{3:^10s}|".format(id,name,age,classid))
			print("="*20)
		except Exception as e:
			print("查询错误",e)

	def delete(self,id):
		'''删除数据'''
		try:
			sql = "delete from stu where id = {};".format(id)
			self.cursor.execute(sql)
			self.db.commit()
			self.findAll()
		except Exception as  e:
			self.db.rollback()
			print("删除失败",e)

	def insert(self,data):
		try:
			self.cursor.execute("insert into stu(name,age,classid) values('%s','%d','%s')"%(data['name'],data['age'],data['classid']))
			self.db.commit()
			self.findAll()
		except Exception as err:
			self.db.rollback()
			print("插入数据失败",err)


st = Stu()

while True:
	print("="*6,"Management system" ,"="*6)
	print("{0:1} {1:13} {2:16}".format(" ","1.查看信息","2.添加信息"))
	print("{0:1} {1:13} {2:16}".format(" ","3.删除信息","4.退出系统"))
	print("="*31)
	key = input("请输入您的选择：")

	#查看学员信息
	if key == '1':
		st.findAll()
		input("按回车键继续：")

	#添加学员信息
	elif key == '2':
		data = {}
		# id 自增无需插入
		data['name'] = input("请输入要添加的学生姓名：")
		data['age'] = int(input("请输入要添加的学生年龄："))
		data['classid'] = input("请输入要添加的学生班级号：")
		st.insert(data)
		input("按回车键继续：")

	#删除学员信息
	elif key == '3':
		st.findAll()
		id = int(input("请输入要删除的学员id号："))
		st.dele(id)
		input("按回车键继续：")

	#退出系统
	elif key == '4':
		print("欢迎使用")
		break

	#异常提示
	else:
		print("========无效输入==========")

		
del student



















